DEFINE s_cargcntrlqty float;
DEFINE s_approveddate CHAR(20);
--Define Working variables
DEFINE tableName CHAR(25);
DEFINE currentDay DATE;
DEFINE mode CHAR(1);
DEFINE sqlErr INT;
DEFINE isamErr INT;
-- Trap Exception
ON EXCEPTION SET sqlErr, isamErr
CALL reportErr(currentDay,tableName,mode, s_b3iid, sqlErr,isamErr);
END EXCEPTION WITH RESUME;
SET LOCK MODE TO WAIT 60;
LET currentDay = today;
LET tableName = 'B3';
LET mode = 'I';
LET s_b3iid = NULL;
FOREACH WITH HOLD
SELECT b3iid, liiclientno, liiaccountno, liibrchno, liirefno, acctsecurno, b3type,
cargcntrlno, carriercode, createdate, custoff, k84date, modetransp,
portunlading, reldate, status, totb3duty, totb3exctax, totb3gst,
totb3sima, totb3vfd, transno, weight, purchaseorder1, purchaseorder2,
shipvia, locationofgoods, containerno, vendorname, vendorstate, vendorzip,
freight, usportexit, billoflading, cargcntrlqty, approveddate
INTO s_b3iid, s_liiclientno, s_liiaccountno, s_liibrchno, s_liirefno, s_acctsecurno
,
s_b3type, s_cargcntrlno, s_carriercode, s_createdate, s_custoff, s_k84date,
s_modetransp, s_portunlading, s_reldate, s_status, s_totb3duty,
s_totb3exctax, s_totb3gst, s_totb3sima, s_totb3vfd, s_transno, s_weight,
s_purchaseorder1, s_purchaseorder2, s_shipvia, s_locationofgoods, s_containerno,
s_vendorname, s_vendorstate, s_vendorzip, s_freight, s_usportexit,
s_billoflading, s_cargcntrlqty, s_approveddate
FROM ip_0p@ipdb:informix.b3
-- WHERE approveddate >= '2011/03' and approveddate < '2011/04'
WHERE approveddate >= startdate and approveddate < enddate
BEGIN
-- Trap Exception
ON EXCEPTION SET sqlErr, isamErr
CALL reportErr(currentDay,tableName,mode, s_b3iid, sqlErr,isamErr);
END EXCEPTION WITH RESUME;
insert into b3
values(s_b3iid, s_liiclientno, s_liiaccountno, s_liibrchno, s_liirefno, s_acctsecurn
o,
s_b3type, s_cargcntrlno, s_carriercode, s_createdate, s_custoff, s_k84date,
s_modetransp, s_portunlading, s_reldate, s_status, s_totb3duty,
s_totb3exctax, s_totb3gst, s_totb3sima, s_totb3vfd, s_transno, s_weight,
s_purchaseorder1, s_purchaseorder2, s_shipvia, s_locationofgoods, s_containerno,
s_vendorname, s_vendorstate, s_vendorzip, s_freight, s_usportexit,
s_billoflading, s_cargcntrlqty, s_approveddate);
END
END FOREACH;